﻿using Composr.Core;
using Composr.Repository.Dapper;
using System.Collections.Generic;
using System.Data;
using Dapper;
using System.Linq;
using CodeLabs.Util;
using Composr.Core.Repositories;

namespace Composr.Repository
{
    public class BlogRepository : Composr.Core.Repositories.BlogRepository
    {

        public BlogRepository()
        {
            Locale = Locale.EN;
        }

        public Locale Locale { get; set; }

        public Blog Get(int id)
        {
            return Fetch(id, Locale);
        }

        public IList<Blog> Get(Filter filter)
        {
            if (filter == null) filter = new Filter();
            return Fetch(filter.Criteria, Locale, filter.Offset, filter.Limit);
        }

        /// <summary>
        /// if blogid is null, then creates a new blog, otherwise updates an existing one
        /// </summary>
        /// <param name="blog"></param>
        /// <returns></returns>
        public Blog Save(Blog blog)
        {
            int blogid = 0;
            if(blog.BlogID.HasValue)
            {
                blogid = blog.BlogID.Value;
                Update(blog);
            }
            else
            {
                blogid = Create(blog);
            }

            return Fetch(blogid, blog.Locale);
        }

        public void Delete(Blog blog)
        {
            MarkDeleted(blog);
        }

        public int Count(string criteria)
        {
            return Count(criteria, Locale);
        }

        private int Count(string criteria, Core.Locale locale)
        {
            string sql = "SELECT COUNT(1) FROM Blogs WHERE LocaleID = @LocaleID AND ISNULL(Deleted, 0) = 0";
            if (!criteria.IsBlank())
            {
                sql += " AND (Name LIKE @Criteria OR Description LIKE @Criteria)";
            }

            using (IDbConnection conn = ConnectionFactory.CreateConnection())
            {
                criteria = "%" + criteria + "%";
                return conn.Query<int>(sql, new { Criteria = criteria, LocaleID = (int)locale }).SingleOrDefault();
            }
        }

        /// <summary>
        /// inserts a new blog record in the db
        /// </summary>
        /// <param name="blog"></param>
        /// <returns></returns>
        private int Create(Blog blog)
        {
            string sql = "INSERT INTO [Blogs] ([BlogID], [LocaleID], [Name], [Description], [URL]) OUTPUT inserted.[BlogID] VALUES (NEXT VALUE FOR dbo.BlogIDSequence, @LocaleID, @Name, @Description, @URL)";
            using (IDbConnection conn = ConnectionFactory.CreateConnection())
            {
                return conn.Query<int>(sql, new
                {
                    LocaleID = (int)blog.Locale,
                    Name = blog.Name,
                    Description = blog.Description,
                    URL = blog.Url
                }
                ).Single();
            }
        }

        /// <summary>
        /// gets the blog matching the id and locale
        /// </summary>
        /// <param name="blogid"></param>
        /// <param name="locale"></param>
        /// <returns></returns>
        private Blog Fetch(int blogid, Locale locale)
        {
            string sql = "SELECT [BlogID], [LocaleID] as Locale, [Name], [Description], [URL] FROM Blogs WHERE BlogID = @BlogId AND LocaleID = @LocaleID AND ISNULL(Deleted, 0) = 0";

            using (IDbConnection conn = ConnectionFactory.CreateConnection())
            {
                Blog b = conn.Query<Blog>(sql, new { BlogID = blogid, LocaleID = (int)locale }).SingleOrDefault();
                return b;
            }
        }

        /// <summary>
        /// gets the blog matching the id and locale
        /// </summary>
        /// <param name="blogid"></param>
        /// <param name="locale"></param>
        /// <returns></returns>
        private IList<Blog> Fetch(string criteria, Locale locale, int? offset, int? limit)
        {
            string sql = "SELECT [BlogID], [LocaleID] as Locale, [Name], [Description], [URL] FROM Blogs WHERE LocaleID = @LocaleID AND ISNULL(Deleted, 0) = 0 ";

            if(!criteria.IsBlank()) sql += " AND (Name LIKE @Criteria OR Description LIKE @Criteria)";
            sql += " ORDER BY [Name]";
            if(offset.HasValue && limit.HasValue) sql += " OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY";

            using (IDbConnection conn = ConnectionFactory.CreateConnection())
            {
                criteria = "%" + criteria + "%";
                return conn.Query<Blog>(sql, new { Criteria = criteria, LocaleID = (int)locale , Offset = offset, Limit = limit}).ToList();
            }
        }
        
        private void Update(Blog blog)
        {
            string sql = "UPDATE Blogs SET [Name] = @Name, [Description] = @Description, [URL] = @URL WHERE BlogID = @BlogId AND LocaleID = @LocaleID";

            using (IDbConnection conn = ConnectionFactory.CreateConnection())
            {
                conn.Query<Blog>(sql, new { BlogID = blog.BlogID, LocaleID = (int)blog.Locale, Name = blog.Name, Description = blog.Description, URL = blog.Url });
            }
        }

        private void MarkDeleted(Blog blog)
        {
            string sql = "UPDATE Blogs SET Deleted = 1 WHERE BlogID = @BlogId AND LocaleID = @LocaleID";

            using (IDbConnection conn = ConnectionFactory.CreateConnection())
            {
                conn.Query<Blog>(sql, new { BlogID = blog.BlogID, LocaleID = (int)blog.Locale});
            }
        }
    }
}